<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*"%>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");

						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);

							// Create the prepared statement and use it to
							// INSERT the student username INTO the account table.
							PreparedStatement pstmt = conn
									.prepareStatement("INSERT INTO account VALUES (?, ?, ?)");
							pstmt.setString(1, request.getParameter("username"));
							pstmt.setString(2, "ChangeMe");
							pstmt.setString(3, request.getParameter("role"));
							pstmt.executeUpdate();
							
							// INSERT the student attributes INTO the student table.
							pstmt = conn.prepareStatement("INSERT INTO student VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
							pstmt.setString(1, request.getParameter("student_id"));
							pstmt.setString(2, request.getParameter("residency"));
							pstmt.setString(3, request.getParameter("first_name"));
							pstmt.setString(4, request.getParameter("middle_name"));
							pstmt.setString(5, request.getParameter("last_name"));
							pstmt.setInt(6, Integer.parseInt(request.getParameter("ssn")));
							pstmt.setString(7, request.getParameter("enrollment"));
							pstmt.setString(8, request.getParameter("username"));
							pstmt.executeUpdate();
							
							// INSERT the additional attributes INTO the undergrad_student table.
							pstmt = conn.prepareStatement("INSERT INTO undergrad_student VALUES (?, ?, ?, ?)");		
							pstmt.setString(1, request.getParameter("student_id"));
							pstmt.setString(2, request.getParameter("college"));
							pstmt.setString(3, request.getParameter("major"));
							pstmt.setString(4, request.getParameter("minor"));
							pstmt.executeUpdate();
							
							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // UPDATE the student attributes in the Student table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "UPDATE student SET residency = ?, first_name = ?, " +
	                            "middle_name = ?, last_name = ?, ssn = ?, enrollment_status = ? " +
	                            "WHERE student_id = ?");

	                        pstmt.setString(1, request.getParameter("residency"));
	                        pstmt.setString(2, request.getParameter("first_name"));
	                        pstmt.setString(3, request.getParameter("middle_name"));
	                        pstmt.setString(4, request.getParameter("last_name"));
	                        pstmt.setInt(5, Integer.parseInt(request.getParameter("ssn")));
	                        pstmt.setString(6, request.getParameter("enrollment"));
	                        pstmt.setString(7, request.getParameter("student_id"));
	                        pstmt.executeUpdate();
	                        
	                        pstmt = conn.prepareStatement(
		                    	"UPDATE undergrad_student SET college = ?, major = ?, minor = ? " +
		                    	"WHERE student_id = ?");
	                        pstmt.setString(1, request.getParameter("college"));
	                        pstmt.setString(2, request.getParameter("major"));
	                        pstmt.setString(3, request.getParameter("minor"));
	                        pstmt.setString(4, request.getParameter("student_id"));
	                        pstmt.executeUpdate();

	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						if (action != null && action.equals("delete")) {
							conn.setAutoCommit(false);
							String student_id = request.getParameter("student_id");
							
							//delete attendance
							PreparedStatement pstmt = conn.prepareStatement("DELETE FROM attendance WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete past degree 
							pstmt = conn.prepareStatement("DELETE FROM degree WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete probation
							pstmt = conn.prepareStatement("DELETE FROM probation WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//enrollment
							pstmt = conn.prepareStatement("DELETE FROM enroll WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete undergraduate 
							pstmt = conn.prepareStatement("DELETE FROM undergrad_student WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete student
							pstmt = conn.prepareStatement("DELETE FROM student WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						// Create the statement
						Statement statement = conn.createStatement();

						// Use the created statement to SELECT
						// the student attributes FROM the student table.
						ResultSet rs = statement.executeQuery("SELECT * FROM student s, undergrad_student u " + 
								"WHERE s.student_id = u.student_id");
				%>
				<table border="1">
					<tr>
						<th>Student ID</th>
						<th>Username</th>
						<th>First name</th>
						<th>Middle name</th>
						<th>Last name</th>
						<th>Residency</th>
						<th>SSN</th>
						<th>Enrollment</th>
						<th>College</th>
						<th>Major</th>
						<th>Minor</th>
					</tr>
					<tr>
						<form action="undergrad_student.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							<input type="hidden" value="student" name="role">
							<th><input value="" name="student_id" size="10"></th>
							<th><input value="" name="username" size="15"></th>
							<th><input value="" name="first_name" size="15"></th>
							<th><input value="" name="middle_name" size="15"></th>
							<th><input value="" name="last_name" size="15"></th>
							<th>
								<select name="residency" style="width:100px">
									<option value="California">California resident</option>
									<option value="non-CA">Non-CA US student</option>
									<option value="foreign">foreign student</option>
								</select>
							</th>
							<th><input value="" name="ssn" size="10"></th>
							<th>
								<select name="enrollment" style="width:100px">
									<option value="YES">Enrolled</option>
									<option value="NO">Not enrolled</option>
								</select>
							</th>
							<th>
								<select name="college" style="width:100px">
									<option value="Revelle">Revelle College</option>
									<option value="Muir">John Muir College</option>
									<option value="Marshall">Thurgood Marshall College</option>
									<option value="Warren">Earl Warren College</option>
									<option value="Roosevelt">Eleanor Roosevelt College</option>
									<option value="Sixth">Sixth College</option>
								</select>
							</th>
							<th><input value="" name="major" size="15"></th>
							<th><input value="" name="minor" size="15"></th>
							<th><input type="submit" value="Insert"></th>
						</form>
					</tr>
					<%
							// Iterate over the ResultSet
							while (rs.next()) {
					%>
					<tr>
                        <form action="undergrad_student.jsp" method="POST">
                            <input type="hidden" value="update" name="action">
                            <td>
                            	<input type="hidden" value="<%= rs.getString("student_id") %>" name="student_id">
                                <%= rs.getString("student_id") %>
                            </td>
                            <td>
                                <input type="hidden" value="<%= rs.getString("username") %>" name="username">
                                <%= rs.getString("username") %>
                            </td>
                            <td>
                                <input value="<%= rs.getString("first_name") %>" name="first_name" size="15">
                            </td>
                            <td>
                                <input value="<%= rs.getString("middle_name") %>" name="middle_name" size="15">
                            </td>
                            <td>
                                <input value="<%= rs.getString("last_name") %>" name="last_name" size="15">
                            </td>
                            <td>
                                <% String residency = rs.getString("residency"); %>
                                <select name="residency" style="width:100px">
									<option value="California" <% if (residency.equals("California")) out.print("selected='selected'"); %>>California resident</option>
									<option value="non-CA" <% if (residency.equals("non-CA")) out.print("selected='selected'"); %>>Non-CA US student</option>
									<option value="foreign" <% if (residency.equals("foreign")) out.print("selected='selected'"); %>>foreign student</option>
								</select>
                            </td>
                            <td>
                                <input value="<%= rs.getInt("ssn") %>" name="ssn" size="10">
                            </td>
                            <td>
                            	<% String enrollment = rs.getString("enrollment_status"); %>
                            	<select name="enrollment" style="width:100px">
									<option value="YES" <% if (enrollment.equals("YES")) out.print("selected='selected'"); %>>Enrolled</option>
									<option value="NO" <% if (enrollment.equals("NO")) out.print("selected='selected'"); %>>Not enrolled</option>
								</select>
                            </td>
                            <td>
                            	<% String college = rs.getString("college"); %>
                                <select name="college" style="width:100px">
									<option value="Revelle" <% if (college.equals("Revelle")) out.print("selected='selected'"); %>>Revelle College</option>
									<option value="Muir" <% if (college.equals("Muir")) out.print("selected='selected'"); %>>John Muir College</option>
									<option value="Marshall" <% if (college.equals("Marshall")) out.print("selected='selected'"); %>>Thurgood Marshall College</option>
									<option value="Warren" <% if (college.equals("Warren")) out.print("selected='selected'"); %>>Earl Warren College</option>
									<option value="Roosevelt" <% if (college.equals("Roosevelt")) out.print("selected='selected'"); %>>Eleanor Roosevelt College</option>
									<option value="Sixth" <% if (college.equals("Sixth")) out.print("selected='selected'"); %>>Sixth College</option>
								</select>
                            </td>
                            <td>
                                <input value="<%= rs.getString("major") %>" name="major" size="15">
                            </td>
                            <td>
                                <input value="<%= rs.getString("minor") %>" name="minor" size="15">
                            </td>
                            <td>
                                <input type="submit" value="Update">
                            </td>
                        </form>
                        <form action="undergrad_student.jsp" method="POST">
                            <input type="hidden" value="delete" name="action">
                            <input type="hidden" 
                                value="<%= rs.getString("student_id") %>" name="student_id">
                            <td>
                                <input type="submit" value="Delete">
                            </td>
                        </form>
                    </tr>
                    </tr>
					<%
							}
					%>
					<%
						} catch (SQLException e) {
							System.out.println(e.getMessage());
							conn.rollback();
						} catch (Exception e) {
							System.out.println(e.getMessage());
							conn.rollback();
						}
					%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>